﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using Microsoft.TV2.Tools;
using System.Data;

namespace UDS.DB.Lib
{
    internal static class Installer
    {
        /// <summary>
        /// The main entry of this application.
        /// </summary>
        [STAThread]
        internal static void Main(string[] args)
        {
            var cmdArgs = new CmdArgs();
            try
            {
                ArgParser.Parse(cmdArgs, args);
                if (cmdArgs.ShowUsage)
                {
                    cmdArgs.Usage(null);
                    return;
                }
            }
            catch (Exception exp)
            {
                cmdArgs.Usage(exp.Message);
                return;
            }

            bool successful = false;

            // Read the connection string
            string cs = ConfigurationManager.AppSettings["ConnectionString"];
            if (string.IsNullOrEmpty(cs))
            {
                Logger.WriteError("The configuration key 'ConnectionString' is required!");
                goto END;
            }

            // Read tables/views.
            ObjectDependencies tableRelationships = ObjectDependencies.LoadTables();
            ObjectDependencies viewRelationships = ObjectDependencies.LoadViews();
            var tableList = new List<string>();
            var viewList = new List<string>();
            tableRelationships.VisitObjects(name => tableList.Add(name));
            viewRelationships.VisitObjects(name => viewList.Add(name));

            Logger.Init();
            // Logger.WriteLine("Start to install database...");

            try
            {
                // Checks whether the connection string is valid? 
                var csBuilder = new SqlConnectionStringBuilder(cs);
                if (!string.IsNullOrEmpty(cmdArgs.Server))
                {
                    csBuilder.DataSource = cmdArgs.Server;
                }

                if (string.IsNullOrEmpty(csBuilder.DataSource))
                {
                    Logger.WriteError("The data source is missing in the connection string.");
                    goto END;
                }
                if (string.IsNullOrEmpty(csBuilder.InitialCatalog))
                {
                    Logger.WriteError("The database name is missing in the connection string.");
                    goto END;
                }

                Logger.WriteLine("Verify the db '{0}' already exists on server '{1}'...", csBuilder.InitialCatalog, csBuilder.DataSource);
                VerifyDBExists(csBuilder);

                Logger.WriteLine("");
                Logger.WriteLine("Start to install database {0} at {1}...", csBuilder.InitialCatalog, csBuilder.DataSource);

                using (var sqlConnection = new DBConnection(csBuilder.ConnectionString))
                {
                    try
                    {
                        // Open the database connection and begin a transaction
                        sqlConnection.Open();
                        sqlConnection.BeginTransaction();

                        // Drop existing stored procedures/functions?
                        // The two options must be expliciltly specified.
                        if (cmdArgs.DropStoredProcedures)
                        {
                            // Note: this option is used to ensure that the old stored procedures are totally removed from the DB.
                            DBUtil.DropAllStoredProcedures(sqlConnection);
                        }
                        if (cmdArgs.DropFunctions)
                        {
                            DropFunctions(sqlConnection);
                        }

                        // Drop existing tables/views?
                        if (cmdArgs.Rebuild || cmdArgs.DropViews)
                        {
                            DropViews(sqlConnection, viewList);
                        }
                        if (cmdArgs.Rebuild || cmdArgs.DropTables)
                        {
                            DropTables(sqlConnection, tableList);
                        }
                        if (cmdArgs.Rebuild || cmdArgs.UpdateFunctions)
                        {
                            UpdateFunctions(sqlConnection);
                        }
                        if (cmdArgs.Rebuild || cmdArgs.CreateTables)
                        {
                            CreateTables(sqlConnection, tableList);
                        }
                        if (cmdArgs.Rebuild || cmdArgs.CreateViews)
                        {
                            CreateViews(sqlConnection, viewList);
                        }
                        if (cmdArgs.Rebuild || cmdArgs.UpdateStoredProcedures)
                        {
                            UpdateStoredProcedures(sqlConnection);
                        }

                        // Commit the transaction
                        sqlConnection.CommitTransaction();
                        successful = true;
                    }
                    catch (Exception exp)
                    {
                        Logger.LogException(exp);
                    }
                }

                Logger.WriteLine(null);
                Logger.WriteLine(@"The DB was successfully created. Verify the 'NT AUTHORITY\NETWORK SERVICE' account has access permissions...");
                DBUtil.VerifyNetworkServiceLogin(csBuilder);
            }
            catch (Exception exp)
            {
                successful = false;
                Logger.LogException(exp);
            }

        END:
            // The end of the program.
            Logger.WriteLine();
            Logger.WriteLine(successful ? "Install succeeded!" : "Install failed!");
            Logger.WriteLine();
            Logger.Flush();
        }

        private static void VerifyDBExists(SqlConnectionStringBuilder csBuilder)
        {
            string dbName = csBuilder.InitialCatalog;
            DBUtil.VerifyIdentifierName(dbName, true);

            try
            {
                csBuilder.InitialCatalog = "master";
                using (var connection = new DBConnection(csBuilder.ConnectionString))
                {
                    connection.Open();

                    bool exists = false;
                    using (var dr = connection.ExecuteReader(string.Format("SELECT [name] FROM sys.databases WHERE [name] = '{0}';", dbName), null))
                    {
                        if (dr.Read())
                        {
                            exists = true;
                        }
                    }

                    if (!exists)
                    {
                        Logger.WriteLine("The requested db '{0}' does not exist.", dbName);
                        Logger.WriteLine("Creating db '{0}'...", dbName);

                        connection.ExecuteSQL(string.Format("CREATE DATABASE [{0}];", dbName), null);
                    }
                }
            }
            finally
            {
                csBuilder.InitialCatalog = dbName;
            }
        }

        private static void DropTables(DBConnection sqlConnection, IList<string> tables)
        {
            if (tables != null && tables.Count > 0)
            {
                Logger.WriteLine();
                Logger.WriteLine("Dropping existing tables...");
                for (int i = tables.Count - 1; i >= 0; i--)
                {
                    string table = tables[i];
                    Logger.WriteLine(tables.Count - i, table);
                    DBUtil.DropTable(sqlConnection, table);
                }
            }
        }

        private static void DropViews(DBConnection sqlConnection, IList<string> views)
        {
            if (views != null && views.Count > 0)
            {
                Logger.WriteLine();
                Logger.WriteLine("Dropping existing views...");
                for (int i = views.Count - 1; i >= 0; i--)
                {
                    string view = views[i];
                    Logger.WriteLine(views.Count - i, view);
                    DBUtil.DropView(sqlConnection, view);
                }
            }
        }

        private static void CreateViews(DBConnection sqlConnection, IList<string> views)
        {
            if (views != null && views.Count > 0)
            {
                Logger.WriteLine();
                Logger.WriteLine("Creating views...");
                DBUtil.ExecuteEmbededResources(sqlConnection, "UDS.DB.Views", views);
            }
        }

        private static void CreateTables(DBConnection sqlConnection, IList<string> tables)
        {
            if (tables != null && tables.Count > 0)
            {
                Logger.WriteLine();
                Logger.WriteLine("Creating tables...");
                DBUtil.ExecuteEmbededResources(sqlConnection, "UDS.DB.Tables", tables);
            }
        }

        private static void DropFunctions(DBConnection sqlConnection)
        {
            Logger.WriteLine();
            Logger.WriteLine("Dropping existing functions...");

            var regex = new Regex(@"^UDS\.DB\.Functions\.(fn_\w+)\.sql$", RegexOptions.Compiled | RegexOptions.IgnoreCase);
            DBUtil.ExecuteEmbededResources(sqlConnection, regex, kvp => DBUtil.DropFunction(sqlConnection, kvp.Key));
        }

        private static void UpdateFunctions(DBConnection sqlConnection)
        {
            Logger.WriteLine();
            Logger.WriteLine("Updating functions...");

            var regex = new Regex(@"^UDS\.DB\.Functions\.(fn_\w+)\.sql$", RegexOptions.Compiled | RegexOptions.IgnoreCase);
            DBUtil.ExecuteEmbededResources(sqlConnection, regex);
        }

        private static void UpdateStoredProcedures(DBConnection sqlConnection)
        {
            Logger.WriteLine();
            Logger.WriteLine("Updating stored procedures...");

            var regex = new Regex(@"^UDS\.DB\.SP.*\.(sproc_\w+)\.sql$", RegexOptions.Compiled | RegexOptions.IgnoreCase);
            DBUtil.ExecuteEmbededResources(sqlConnection, regex);
        }
    }
}